package com.task.scheduler.persistence.repository;

import com.task.scheduler.enums.TaskStatus;
import com.task.scheduler.persistence.entity.TaskExecutionEntity;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

@Repository
public interface TaskExecutionRepository extends JpaRepository<TaskExecutionEntity, Long> {

    // ==================== 基础查询方法 ====================

    List<TaskExecutionEntity> findByTaskId(String taskId);

    /**
     * 查找未删除的执行记录
     */
    List<TaskExecutionEntity> findByDeletedFalse();

    /**
     * 分页查找未删除的执行记录
     */
    Page<TaskExecutionEntity> findByDeletedFalse(Pageable pageable);

    /**
     * 根据ID查找未删除的执行记录
     */
    Optional<TaskExecutionEntity> findByIdAndDeletedFalse(Long id);

    /**
     * 根据任务ID查找未删除的执行记录
     */
    List<TaskExecutionEntity> findByTaskIdAndDeletedFalse(String taskId);

    /**
     * 分页根据任务ID查找未删除的执行记录
     */
    Page<TaskExecutionEntity> findByTaskIdAndDeletedFalse(String taskId, Pageable pageable);

    /**
     * 根据任务ID查找最新的执行记录
     */
    Optional<TaskExecutionEntity> findFirstByTaskIdAndDeletedFalseOrderByCreatedAtDesc(String taskId);

    // ==================== 状态查询 ====================

    /**
     * 根据状态查找未删除的执行记录
     */
    List<TaskExecutionEntity> findByStatusAndDeletedFalse(TaskStatus status);

    /**
     * 分页根据状态查找未删除的执行记录
     */
    Page<TaskExecutionEntity> findByStatusAndDeletedFalse(TaskStatus status, Pageable pageable);

    /**
     * 根据任务ID和状态查找未删除的执行记录
     */
    List<TaskExecutionEntity> findByTaskIdAndStatusAndDeletedFalse(String taskId, TaskStatus status);

    /**
     * 根据状态列表查找未删除的执行记录
     */
    @Query("SELECT e FROM TaskExecutionEntity e WHERE e.status IN :statuses AND e.deleted = false")
    List<TaskExecutionEntity> findByStatusesAndNotDeleted(@Param("statuses") List<TaskStatus> statuses);

    /**
     * 查找正在执行的任务记录
     */
    List<TaskExecutionEntity> findByStatusAndDeletedFalseOrderByStartTimeDesc(TaskStatus status);

    /**
     * 查找待执行的任务记录
     */
    List<TaskExecutionEntity> findByStatusAndDeletedFalseOrderByCreatedAtAsc(TaskStatus status);

    // ==================== 重试相关查询 ====================

    /**
     * 根据重试次数查找执行记录
     */
    List<TaskExecutionEntity> findByRetryCountAndDeletedFalse(int retryCount);

    /**
     * 查找重试次数大于指定值的执行记录
     */
    List<TaskExecutionEntity> findByRetryCountGreaterThanAndDeletedFalse(int retryCount);

    /**
     * 查找可以重试的失败任务
     */
    @Query("SELECT e FROM TaskExecutionEntity e WHERE e.deleted = false " +
           "AND e.status = 'FAILED' " +
           "AND e.retryCount < :maxRetry")
    List<TaskExecutionEntity> findRetryableTasks(@Param("maxRetry") int maxRetry);

    // ==================== 时间范围查询 ====================

    /**
     * 查找指定时间范围内开始执行的记录
     */
    List<TaskExecutionEntity> findByStartTimeBetweenAndDeletedFalse(LocalDateTime startTime, LocalDateTime endTime);

    /**
     * 分页查找指定时间范围内开始执行的记录
     */
    Page<TaskExecutionEntity> findByStartTimeBetweenAndDeletedFalse(LocalDateTime startTime, LocalDateTime endTime, Pageable pageable);

    /**
     * 查找指定时间范围内结束执行的记录
     */
    List<TaskExecutionEntity> findByEndTimeBetweenAndDeletedFalse(LocalDateTime startTime, LocalDateTime endTime);

    /**
     * 查找指定时间范围内创建的记录
     */
    List<TaskExecutionEntity> findByCreatedAtBetweenAndDeletedFalse(LocalDateTime startTime, LocalDateTime endTime);

    /**
     * 分页查找指定时间范围内创建的记录
     */
    Page<TaskExecutionEntity> findByCreatedAtBetweenAndDeletedFalse(LocalDateTime startTime, LocalDateTime endTime, Pageable pageable);

    /**
     * 查找执行时间超过指定时长的记录
     */
    @Query("SELECT e FROM TaskExecutionEntity e WHERE e.deleted = false " +
           "AND e.startTime IS NOT NULL " +
           "AND (e.endTime IS NULL OR TIMESTAMPDIFF(SECOND, e.startTime, e.endTime) > :durationSeconds)")
    List<TaskExecutionEntity> findLongRunningExecutions(@Param("durationSeconds") long durationSeconds);

    /**
     * 查找超时的执行记录（开始时间超过指定时长但未结束）
     */
    @Query("SELECT e FROM TaskExecutionEntity e WHERE e.deleted = false " +
           "AND e.status = 'PROCESSING' " +
           "AND e.startTime < :timeoutThreshold")
    List<TaskExecutionEntity> findTimeoutExecutions(@Param("timeoutThreshold") LocalDateTime timeoutThreshold);

    // ==================== 统计查询 ====================

    /**
     * 统计未删除的执行记录总数
     */
    long countByDeletedFalse();

    /**
     * 根据任务ID统计未删除的执行记录数量
     */
    long countByTaskIdAndDeletedFalse(String taskId);

    /**
     * 根据状态统计未删除的执行记录数量
     */
    long countByStatusAndDeletedFalse(TaskStatus status);

    /**
     * 根据任务ID和状态统计执行记录数量
     */
    long countByTaskIdAndStatusAndDeletedFalse(String taskId, TaskStatus status);

    /**
     * 统计指定时间范围内的执行记录数量
     */
    long countByCreatedAtBetweenAndDeletedFalse(LocalDateTime startTime, LocalDateTime endTime);

    /**
     * 统计成功率
     */
    @Query("SELECT CAST(COUNT(CASE WHEN e.status = 'SUCCESS' THEN 1 END) AS DOUBLE) / COUNT(*) " +
           "FROM TaskExecutionEntity e WHERE e.deleted = false " +
           "AND e.createdAt BETWEEN :startTime AND :endTime")
    Double calculateSuccessRate(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    /**
     * 统计平均执行时长（毫秒）
     */
    @Query("SELECT AVG(TIMESTAMPDIFF(MICROSECOND, e.startTime, e.endTime) / 1000) " +
           "FROM TaskExecutionEntity e WHERE e.deleted = false " +
           "AND e.startTime IS NOT NULL AND e.endTime IS NOT NULL " +
           "AND e.createdAt BETWEEN :startTime AND :endTime")
    Double calculateAverageExecutionTime(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    // ==================== 自定义查询 ====================

    /**
     * 查找最近的N个执行记录
     */
    @Query("SELECT e FROM TaskExecutionEntity e WHERE e.deleted = false ORDER BY e.createdAt DESC")
    List<TaskExecutionEntity> findRecentExecutions(Pageable pageable);

    /**
     * 根据任务ID列表查找执行记录
     */
    @Query("SELECT e FROM TaskExecutionEntity e WHERE e.taskId IN :taskIds AND e.deleted = false")
    List<TaskExecutionEntity> findByTaskIdsAndNotDeleted(@Param("taskIds") List<String> taskIds);

    /**
     * 查找需要清理的旧执行记录
     */
    @Query("SELECT e FROM TaskExecutionEntity e WHERE e.deleted = true " +
           "AND e.updatedAt < :cutoffTime")
    List<TaskExecutionEntity> findExecutionsForCleanup(@Param("cutoffTime") LocalDateTime cutoffTime);

    /**
     * 查找异常的执行记录（开始时间为空但状态为PROCESSING）
     */
    @Query("SELECT e FROM TaskExecutionEntity e WHERE e.deleted = false " +
           "AND e.status = 'PROCESSING' AND e.startTime IS NULL")
    List<TaskExecutionEntity> findAnomalousExecutions();

    // ==================== 批量操作 ====================

    /**
     * 批量软删除执行记录
     */
    @Modifying
    @Query("UPDATE TaskExecutionEntity e SET e.deleted = true, e.updatedAt = :updateTime " +
           "WHERE e.id IN :executionIds AND e.deleted = false")
    int batchSoftDelete(@Param("executionIds") List<Long> executionIds, @Param("updateTime") LocalDateTime updateTime);

    /**
     * 批量恢复已删除的执行记录
     */
    @Modifying
    @Query("UPDATE TaskExecutionEntity e SET e.deleted = false, e.updatedAt = :updateTime " +
           "WHERE e.id IN :executionIds AND e.deleted = true")
    int batchRestore(@Param("executionIds") List<Long> executionIds, @Param("updateTime") LocalDateTime updateTime);

    /**
     * 批量更新执行状态
     */
    @Modifying
    @Query("UPDATE TaskExecutionEntity e SET e.status = :newStatus, e.updatedAt = :updateTime " +
           "WHERE e.id IN :executionIds AND e.deleted = false")
    int batchUpdateStatus(@Param("executionIds") List<Long> executionIds, 
                         @Param("newStatus") TaskStatus newStatus, 
                         @Param("updateTime") LocalDateTime updateTime);

    /**
     * 物理删除已软删除的旧执行记录
     */
    @Modifying
    @Query("DELETE FROM TaskExecutionEntity e WHERE e.deleted = true " +
           "AND e.updatedAt < :cutoffTime")
    int physicalDeleteOldExecutions(@Param("cutoffTime") LocalDateTime cutoffTime);

    /**
     * 根据任务ID批量软删除执行记录
     */
    @Modifying
    @Query("UPDATE TaskExecutionEntity e SET e.deleted = true, e.updatedAt = :updateTime " +
           "WHERE e.taskId IN :taskIds AND e.deleted = false")
    int batchSoftDeleteByTaskIds(@Param("taskIds") List<String> taskIds, @Param("updateTime") LocalDateTime updateTime);

    // ==================== 存在性检查 ====================

    /**
     * 检查指定ID的未删除执行记录是否存在
     */
    boolean existsByIdAndDeletedFalse(Long id);

    /**
     * 检查指定任务ID是否有未删除的执行记录
     */
    boolean existsByTaskIdAndDeletedFalse(String taskId);

    /**
     * 检查指定任务ID是否有指定状态的执行记录
     */
    boolean existsByTaskIdAndStatusAndDeletedFalse(String taskId, TaskStatus status);
}
